﻿DELIMITER //

--
--
DROP PROCEDURE IF EXISTS spZeusInventoryAdd
//

-- HU - Leltár
-- EN - Inventory
--
--
CREATE PROCEDURE spZeusInventoryAdd 
  (_AccessToken INTEGER, _PackageCode VARCHAR(20), _LocationCode VARCHAR(20), _Quantity DECIMAL(18,6), _Type VARCHAR(2))
BEGIN
  DECLARE _Rows INTEGER;
  DECLARE _UserID INTEGER;
  DECLARE _Lang VARCHAR(10);

  --
  --
  CALL spAccessTokenCheck(_AccessToken, _UserID, _Lang);


  IF (_Type NOT IN ('1', '2')) THEN

    IF (_Lang = 'HU') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Nem megfelelő típus !';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'What Type is it ? ';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Unangemessen Typ !';
    END IF;
  END IF;


  IF (_Quantity < 0) THEN

    IF (_Lang = 'HU') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Mennyiség nem lehet negatív !';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Quantity could not been less than Zero !';
    END IF;    

    IF (_Lang = 'DE') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Menge kann nicht negativ sein !';
    END IF;
  END IF;


  -- HU - Egyes számoló
  -- EN - 1st team
  --
  IF (_Type = '1') THEN

    -- Update
    --
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION SET _Rows = 0;
  
      UPDATE ZeusInventory
         SET User1 = _UserID,
             Quantity1 = _Quantity,
             Location1 = _LocationCode
       WHERE PackageCode = _PackageCode;

      -- SET PDO::MYSQL_ATTR_FOUND_ROWS => true
      --
      SET _Rows = ROW_COUNT();

    END;

    IF (_Rows = 0) THEN

      -- Insert 
      --
      BEGIN
        DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET _Rows = 0;
    
        INSERT INTO ZeusInventory
          (PackageCode, User1, Location1, Quantity1)
        VALUES
          (_PackageCode, _UserID, _LocationCode, _Quantity);
    
        SET _Rows = 1;
      END;

    END IF;    
  END IF;


  -- HU - Kettes számoló
  -- EN - 2nd team
  --
  IF (_Type = '2') THEN

    -- Update
    --
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION SET _Rows = 0;
  
      UPDATE ZeusInventory
         SET User2 = _UserID,
             Quantity2 = _Quantity,
             Location2 = _LocationCode
       WHERE PackageCode = _PackageCode;

       SET _Rows = ROW_COUNT();

    END;

    IF (_Rows = 0) THEN

      -- Insert 
      --
      BEGIN
        DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET _Rows = 0;
    
        INSERT INTO ZeusInventory
          (PackageCode, User2, Location2, Quantity2)
        VALUES
          (_PackageCode, _UserID, _LocationCode, _Quantity);
    
        SET _Rows = 1;
      END;

    END IF;
  END IF;


  IF (_Rows = 0) THEN

    IF (_Lang = 'HU') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Leltár felvétele ne sikerült !';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Inventory data storing failed !';
    END IF;

    IF (_Lang = 'DE') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Invertar Daten Speichern gescheitert !';
    END IF;
  END IF;

  -- HU - Visszaadja ennek a Rakománynak az adatait
  --   A lokáció és a mennyiség az amit paraméterként kapunk !!!!!
  --
  -- EN - Get the package details
  --   Location AND Quantity MUST BE sam with parameter !!!!!
  --
  SELECT Code, ArticleCode, ArticleName, CAST(Expired as DATE) Expired, 
         
         _LocationCode LocationCode, _Quantity Quantity, UnitCode, '' Notes

    FROM ZeusPackage
   WHERE Code = _PackageCode;

END
//
